﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NENAData.EF;
using NENAData.Factory;
using System.Data.SqlClient;

namespace NENAData.Events
{
    public class EventRegistration
    {

        private static NENA_MembershipEntities GetConnection()
        {
            var x = new SQLMembership();
            return x.DBConnection();
        }

        private static string GetSQLString()
        {
            var x = new SQLMembership();
            return x.GetMembershipDBString();
        }


        public EF.EventRegistration Get(Int32 intEventRegistrationId)
        {
            NENA_MembershipEntities db = GetConnection();

            EF.EventRegistration oEventRegistration = (from er in db.EventRegistrations
                                              where er.EventRegistrationID == intEventRegistrationId
                                              select er).FirstOrDefault();
            return oEventRegistration;
        }

        public EF.EventRegistration Get(Int32 intMemberId, Int32 intEventId)
        {
            NENA_MembershipEntities db = GetConnection();

           EF.EventRegistration oEventRegistration = 
                (from EF.EventRegistration er in 
                     db.EventRegistrations.Include("TransactionPayment")
                            where er.Member.Member_ID == intMemberId &&
                            er.Event.EventId == intEventId
                            select er).FirstOrDefault();
            return oEventRegistration;
        }

        public List<EF.EventRegistration> GetAllSocialRegistrations(Int32 intMemberID)
        {
            NENA_MembershipEntities db = GetConnection();

            var oEventRegistrations = (from er in db.EventRegistrations
                                                       where er.Member.Member_ID == intMemberID &&
                                                       (er.Event.EventId == 3 || er.Event.EventId == 5)
                                                       select er).ToList();
            return oEventRegistrations;
        }

        public void Add(EF.EventRegistration oEventRegistration)
        {

            NENA_MembershipEntities db = GetConnection();

            db.AddToEventRegistrations(oEventRegistration);
            db.SaveChanges();
        }

        /// <summary>
        /// Cannot use EF and LINQ entities 
        /// </summary>
        /// <param name="intMemberId"></param>
        /// <param name="EventId"></param>
        /// <param name="intTransactionPaymentId"></param>
        /// <param name="strComment"></param>
        //public void Add(int intMemberId, int EventId, int intTransactionPaymentId, string strComment)
        //{
        //    NENAData.NENAMember.NENAMember oNenaData = new NENAData.NENAMember.NENAMember();
        //    var oEvents = new NENAData.Events.List();
        //    var oPayments = new NENAData.Transactions.Payments.Payments();

        //    NENA_MembershipEntities db = GetConnection();

        //    EF.EventRegistration oER = new EF.EventRegistration
        //    {
        //        Member = oNenaData.GetMember(intMemberId),
        //        Event = oEvents.GetEvent(EventId),
        //        TransactionPayment  = oPayments.GetTransactionPayment_byTransactionPaymentID(intTransactionPaymentId)

        //    };
        //    db.AddToEventRegistrations(oEventRegistration);
        //    db.SaveChanges();
        //}

        public int AddbyStoredProcedure(int intMemberId, int EventId, int intTransactionPaymentId, string strComment)
        {

            string strSQL = @"sp_NENA_Insert_EventRegistrationsWithReturnIdentity";

            SqlConnection objSqlCon = new SqlConnection(GetSQLString());
            SqlCommand cmd = new SqlCommand(strSQL, objSqlCon);
            cmd.CommandType = System.Data.CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@Member_ID", intMemberId);
            cmd.Parameters.AddWithValue("@Event_Id", EventId);
            cmd.Parameters.AddWithValue("@TransactionPaymentId", intTransactionPaymentId);
            cmd.Parameters.AddWithValue("@Comments", strComment);
            SqlParameter opar = new SqlParameter
            {
                Direction = System.Data.ParameterDirection.ReturnValue,
                DbType = System.Data.DbType.Int32,
                ParameterName = "@Return_Value"

            };
            cmd.Parameters.Add(opar);
            objSqlCon.Open();
            cmd.ExecuteNonQuery();
            int newReq = Convert.ToInt32(opar.Value);
            objSqlCon.Close();

            return newReq;
        }

        public int AddbySQL(int intMemberId, int EventId, int intTransactionPaymentId, string strComment)
        {

            string strSQL = @"return scope_identity() 
                insert into EventRegistrations 
                (Member_ID, Event_Id, TransactionPaymentId, Comments) 
                values (@Member_ID, @Event_Id, @TransactionPaymentId, @Comments)";

            SqlConnection objSqlCon = new SqlConnection(GetSQLString());
            SqlCommand cmd = new SqlCommand(strSQL, objSqlCon);
            cmd.Parameters.AddWithValue("@Member_ID", intMemberId);
            cmd.Parameters.AddWithValue("@Event_Id", EventId);
            cmd.Parameters.AddWithValue("@TransactionPaymentId", intTransactionPaymentId);
            cmd.Parameters.AddWithValue("@Comments", strComment);
            SqlParameter opar = new SqlParameter
            {
                 Direction = System.Data.ParameterDirection.ReturnValue,
                  DbType = System.Data.DbType.Int32,
                   ParameterName = "@Return_Value"

            };
            cmd.Parameters.Add(opar);
            objSqlCon.Open();
            cmd.ExecuteNonQuery();
            int newReq = Convert.ToInt32(opar.Value);  
            objSqlCon.Close();

            return newReq;
        }


    }
}
